package by.epam.communalservices.dao.jdbc;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import by.epam.communalservices.dao.DaoJkhObject;
import by.epam.communalservices.entity.JkhObject;
import by.epam.communalservices.exception.JkhDaoException;

public class JdbcDaoJkhObject implements DaoJkhObject {

    private Connection con;

    private final String COLUMN_OBJECT_ID = "jkh_object_id";
   
    private final String COLUMN_OWNER_ID = "ten_owner_id";

    private final String COLUMN_ADDRESS = "address";

    private final String COLUMN_SURNAME = "ten_surname";

    private final String COLUMN_NAME = "ten_name";

    private final String COLUMN_PATRONYMIC = "ten_patronymic";

    private final String COLUMN_LAST_REPAIR = "last_repair";
    
    static Logger JdbcDaoObjectLoger = Logger.getLogger(JdbcDaoJkhObject.class.getName());
    
    public JdbcDaoJkhObject(Connection con) throws JkhDaoException {
        try {
            if (!con.isClosed())
                this.con = con;
            else {
                throw new JkhDaoException(
                        "Connection error. Connection is not open",
                        new Exception());
            }
        } catch (JkhDaoException jex) {
            throw jex;
        } catch (SQLException sx) {
            JdbcDaoObjectLoger.error("Connection error",sx);
            sx.printStackTrace();
            throw new JkhDaoException("Unknown connection error",
                    new Exception());

        }
    }

    public Long create(JkhObject jkhObj) {
        JdbcDaoObjectLoger.debug("Long create(JkhObject jkhObj)");
        JdbcDaoObjectLoger.info("method create object");
        Long genid = null;
        String sql = "INSERT INTO public.jkh_object(ten_owner_id,address,ten_surname,"
                + "ten_name,ten_patronymic,last_repair) VALUES (?,?,?,?,?,?)";
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql,
                        Statement.RETURN_GENERATED_KEYS);
                pstate.setLong(1, jkhObj.getIdOwner());
                pstate.setString(2, jkhObj.getAddress());
                pstate.setString(3, jkhObj.getSurname());
                pstate.setString(4, jkhObj.getName());
                pstate.setString(5, jkhObj.getPatronymic());
                pstate.setDate(6, jkhObj.getDateLastRepair());
                pstate.execute();
                result = pstate.getGeneratedKeys();
                if (result.next()) {
                    genid = result.getLong(1);
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("query execute error",ex);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return genid;
    }

    public JkhObject read(Long id) {
        JdbcDaoObjectLoger.debug("JkhObject read(Long id)");
        JdbcDaoObjectLoger.info("method reads the object");
        String sql = "SELECT * FROM public.jkh_object WHERE jkh_object_id = ?";
        JkhObject jkhObj = null;
        ResultSet result = null;
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                result = pstate.executeQuery();
            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
            try {
                if (result.next()) {
                    jkhObj = new JkhObject(
                            result.getLong(COLUMN_OBJECT_ID),
                            result.getLong(COLUMN_OWNER_ID),
                            result.getString(COLUMN_ADDRESS),
                            result.getString(COLUMN_SURNAME),
                            result.getString(COLUMN_NAME),
                            result.getString(COLUMN_PATRONYMIC),
                            result.getDate(COLUMN_LAST_REPAIR));
                } else {
                    throw new SQLException();
                }
            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("Record not found",ex);
                ex.printStackTrace();
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return jkhObj;
    }

    public void update(Long id, JkhObject jkhObj) {
        JdbcDaoObjectLoger.debug("update(Long id, JkhObject jkhObj)");
        JdbcDaoObjectLoger.info("method update the object");
        PreparedStatement pstate = null;
        String sql = "UPDATE public.jkh_object SET ten_owner_id=?, address =?, ten_surname = ?, "
                + "ten_name = ?,ten_patronymic = ?,last_repair = ? where jkh_object_id = ?";
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setDouble(1, jkhObj.getIdOwner());
                pstate.setString(2, jkhObj.getAddress());
                pstate.setString(3, jkhObj.getSurname());
                pstate.setString(4, jkhObj.getName());
                pstate.setString(5, jkhObj.getPatronymic());
                pstate.setDate(6, jkhObj.getDateLastRepair());
                pstate.setLong(7, id);
                pstate.execute();
            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
    }

    public void delete(Long id) {
        JdbcDaoObjectLoger.debug("delete(Long id)");
        JdbcDaoObjectLoger.info("method delete the object");
        String sql = "DELETE FROM public.jkh_object where jkh_object_id = ?";
        PreparedStatement pstate = null;
        try {
            try {
                pstate = con.prepareStatement(sql);
                pstate.setLong(1, id);
                pstate.execute();
            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("query execute error",ex);
                ex.printStackTrace();
            }
        } finally {
            if (pstate != null)
                try {
                    pstate.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }

    }

    public List<JkhObject> findAll() {
        @SuppressWarnings("unchecked")
        List<JkhObject> list = Collections.EMPTY_LIST;
        JdbcDaoObjectLoger.debug("List<JkhObject> findAll()");
        JdbcDaoObjectLoger.info("method returns a list of object");
        Statement state = null;
        ResultSet result = null;
        try {
            try {
                String sql = "SELECT * FROM public.jkh_object ORDER BY jkh_object_id";
                state = con.createStatement();
                result = state.executeQuery(sql);
                list = new ArrayList<JkhObject>();
                while (result.next()) {
                    JkhObject jkhObj = new JkhObject(
                            result.getLong(COLUMN_OBJECT_ID),
                            result.getLong(COLUMN_OWNER_ID),
                            result.getString(COLUMN_ADDRESS),
                            result.getString(COLUMN_SURNAME),
                            result.getString(COLUMN_NAME),
                            result.getString(COLUMN_PATRONYMIC),
                            result.getDate(COLUMN_LAST_REPAIR));
                    list.add(jkhObj);
                }

            } catch (SQLException ex) {
                JdbcDaoObjectLoger.error("Error compiling the list",ex);
                ex.printStackTrace(System.out);
            }
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close result",ex);
                    ex.printStackTrace();
                }
            if (state != null)
                try {
                    state.close();
                } catch (SQLException ex) {
                    JdbcDaoObjectLoger.error("Error close statement",ex);
                    ex.printStackTrace();
                }
        }
        return list;
    }

}
